import os
import sys

from fastapi import APIRouter
import pandas as pd
from pydantic import BaseModel
import logging
import traceback
from routes import sqlite
from util.common import select_file

certif_app = APIRouter()


# 添加收件人
@certif_app.post("/add_CertificateCode")
async def add_CertificateCode():
    excelname = select_file('请选择要生成邮件的excel文件')
    if excelname == '':
        return {
            "code": 204,
            "status": "no",
            "message": "没有选择文件",
            "path": '',
            "data": []
        }
    if sqlite.Execute_one_SQL("DELETE FROM CertificateCode"):
        try:
            df = pd.read_excel(excelname, sheet_name='运输报告', usecols=['Item', 'CertificateCode'],
                               dtype={'Item': str})
            if df.empty:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "没有数据",
                }
            if sqlite.insert_dataframe(df, 'CertificateCode'):
                return {
                    "code": 200,
                    "status": "ok",
                    "message": "添加成功",
                }
            else:
                return {
                    "code": 204,
                    "status": "no",
                    "message": "添加失败",
                }
        except Exception as e:
            base_path = os.path.dirname(os.path.realpath(sys.argv[0]))
            log_path = os.path.join(base_path, "log.log")
            # 获取异常信息
            tb = traceback.format_exc()
            # 创建一个logger
            logger = logging.getLogger('my_logger')
            # 设置日志级别
            logger.setLevel(logging.ERROR)

            # 创建一个handler用于写入日志文件
            fh = logging.FileHandler(log_path, encoding='utf-8')

            # 定义handler的输出格式
            formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
            fh.setFormatter(formatter)
            # 给logger添加handler
            logger.addHandler(fh)
            # 记录异常
            logger.error(f"An exception occurred: {e}\n{tb}")
            return {
                "code": 204,
                "status": "no",
                "message": str(e),
            }
    else:
        return {
            "code": 204,
            "status": "no",
            "message": "清空表数据失败",
        }


# 查询收件人
@certif_app.get("/query_CertificateCode")
async def query_CertificateCode(Item: str = "", CertificateCode: str = "", page: int = 1, page_size: int = 10):
    try:
        data_sql = """
        SELECT id,Item, CertificateCode, (SELECT COUNT(*) FROM CertificateCode WHERE Item LIKE ? AND CertificateCode LIKE ?) as total
        FROM CertificateCode
        WHERE Item LIKE ? AND CertificateCode LIKE ?
        LIMIT ? OFFSET ?
        """
        params = (
        '%' + Item + '%', '%' + CertificateCode + '%', '%' + Item + '%', '%' + CertificateCode + '%', page_size,
        (page - 1) * page_size)
        df = sqlite.query(data_sql, params)
        total = int(df.loc[0, 'total'])
        if df.empty:
            return {
                "code": 204,
                "status": "no",
                "message": "没有数据",
                "total": total,  # 返回总记录数
                "data": []
            }

        # 将DataFrame转换为字典列表
        dict_list = df.to_dict(orient='records')
        return {
            "code": 200,
            "status": "ok",
            "message": "success",
            "total": total,  # 返回总记录数
            "data": dict_list
        }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "message": str(e),
        }


# 添加或编辑单个收件人
class AddrecipientsType(BaseModel):
    id: int
    Item: str
    CertificateCode: str


@certif_app.post("/add_oneCertificateCode")
async def add_oneCertificateCode(data: AddrecipientsType):
    sql = f'''
          UPDATE CertificateCode SET Item='{data.Item}',CertificateCode='{data.CertificateCode}' 
          WHERE id={data.id};
                  '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "修改成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "修改失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }

@certif_app.delete("/del_oneCertificateCode")
async def del_oneCertificateCode(id: str):
    sql = f'''
          DELETE FROM CertificateCode 
          WHERE id='{id}';
              '''
    try:
        if sqlite.Execute_one_SQL(sql):
            return {
                "code": 200,
                "status": "ok",
                "msg": "删除成功",
            }
        else:
            return {
                "code": 204,
                "status": "no",
                "msg": "删除失败",
            }
    except Exception as e:
        return {
            "code": 204,
            "status": "no",
            "msg": str(e),
        }
